package com.hnkywl.sts.dao.cwgl;

import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 个人借款
 * User: ccr
 * Date: 13-6-4
 * Time: 上午12:27
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class GrjkDao extends SimpleHibernateTemplate {
    public List<Map> findAll(Date endDate,Long gsId,Long gsjgId,Long ygId){
        String selectCount= "select ygId as \"ygId\",gsJc as \"gsJc\", gsjgJc as \"gsjgJc\", ygXm as \"ygXm\", nvl(sum(ye),0) as \"ye\" from (";
        String select = "select yg.id as ygId,gs.jc as gsJc, gsjg.jc as gsjgJc, yg.xm as ygXm, nvl(sum(fl.jfje),0)-nvl(sum(fl.dfje),0) as ye,gsjg.pxh";
        String from = " from t_biz_qtpzfl fl left join t_biz_qtpz qtpz on (qtpz.id = fl.qtpz_id)\n" +
                "  left join t_sys_yg yg on(yg.kmdm = fl.zydm and yg.deleted = 0)\n" +
                "  left join t_sys_gsjg gsjg on (yg.zgjg_id = gsjg.id)\n" +
                "  left join t_sys_gsjg gs on (gsjg.sjjg_id = gs.id)\n" +
                "  left join t_sys_cykmdmys cydm on(cydm.kmdm = fl.kmdm and cydm.gsid = gsjg.id )";
        StringBuilder where = new StringBuilder();
        where.append("qtpz.jiuqi_insert_flag = 3 and qtpz.zt = 1 and qtpz.deleted = 0 and cydm.codeid = 1900");

        StringBuilder where2 = new StringBuilder();

        String cshjk = "select yg.id as ygId,gs.jc as gsJc, gsjg.jc as gsjgJc, yg.xm as ygXm, grjk.jkje as ye,gsjg.pxh";
        String cshjkfrom = " from T_INIT_GRJK grjk  " +
                "  left join t_sys_yg yg on(yg.id = grjk.yg_id and yg.deleted = 0) " +
                "  left join t_sys_gsjg gsjg on (yg.zgjg_id = gsjg.id) " +
                "  left join t_sys_gsjg gs on (yg.zggs_id = gs.id) " ;

        if(endDate!=null){
            where.append(" and ");
            if (where2.length()!=0)
                where2.append(" and ");
            where.append("qtpz.zdrq <= :zdrq");
            where2.append("grjk.date_created <= :zdrq");
        }
        if(gsId!=null){
            where.append(" and ");
            if (where2.length()!=0)
                where2.append(" and ");
            where.append("gs.id = :gsId");
            where2.append("gs.id = :gsId");
        }
        if(gsjgId!=null){
            where.append(" and ");
            if (where2.length()!=0)
                where2.append(" and ");
            where.append("gsjg.id = :gsjgId");
            where2.append("gsjg.id = :gsjgId");
        }
        if(ygId!=null){
            where.append(" and ");
            if (where2.length()!=0)
                where2.append(" and ");
            where.append("yg.id = :ygId");
            where2.append("yg.id = :ygId");
        }
        String groupBy = " group by gsjg.pxh,yg.xm,gsjg.jc,gs.jc,yg.id";
        String groupByCount = " group by pxh,ygXm,gsjgJc,gsJc,ygId";
        String orderBy = " order by pxh asc";
        SQLQuery query = getSession().createSQLQuery(selectCount+select+from+" where "+where+groupBy+" union all " +cshjk + cshjkfrom+ (where2.length()!=0?" where ":"") + where2 +")"+groupByCount+orderBy);
        if(endDate!=null){
            query.setDate("zdrq",endDate);
        }
        if(gsId!=null){
           query.setLong("gsId",gsId);
        }
        if(gsjgId!=null){
            query.setLong("gsjgId", gsjgId);
        }
        if(ygId!=null){
            query.setLong("ygId", ygId);
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }

    public List<Map> findMx(Long ygId){
        String cshsql = "select DATE_CREATED as \"zdrq\",'初始化个人借款' as \"zy\",nvl(jkje, 0) as \"jfje\",0 as \"dfje\", " +
                "  nvl(jkje, 0)  as \"ye\",0 as \"ord\" ,nvl(jkje, 0)  as \"ye1\" " +
                "  from T_INIT_GRJK grjk where yg_id = :ygId union all " ;
        String tt = "select qtpz.zdrq as \"zdrq\",fl.zy as \"zy\",nvl(jfje, 0) as \"jfje\",nvl(dfje, 0) as \"dfje\",\n" +
                "  nvl(jfje, 0) - nvl(dfje, 0) as ye,row_number() over(order by qtpz.zdrq) as ord\n" +
                "  from t_biz_qtpzfl fl\n" +
                "  left join t_biz_qtpz qtpz on (qtpz.id = fl.qtpz_id)\n" +
                "  left join t_sys_yg yg on (yg.kmdm = fl.zydm and yg.deleted = 0)\n" +
                "  left join t_sys_gsjg gsjg on (yg.zgjg_id = gsjg.id)\n" +
                "  left join t_sys_gsjg gs on (gsjg.sjjg_id = gs.id)\n" +
                "  left join t_sys_cykmdmys cydm on (cydm.kmdm = fl.kmdm and cydm.gsid = gsjg.id)\n" +
                " where qtpz.jiuqi_insert_flag = 3 and qtpz.zt = 1 " +
                "       and qtpz.deleted = 0 and cydm.codeid = 1900 and yg.id = :ygId\n" +
                " order by qtpz.zdrq";
        String sql = "select distinct a.*,sum(b.ye) over(partition by a.ord) as \"ye1\" from ("+tt+") a,("+tt+") b" +
                " where a.ord >= b.ord order by 6";
        SQLQuery sqlQuery = getSession().createSQLQuery(cshsql+sql);
        sqlQuery.setLong("ygId",ygId);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }
}
